Many operations performed on information in a database system are executed using database query language statements, such as statements in the structured query language (SQL). SQL is a non-procedural language for accessing and manipulating data in a relational or object-relational database. Procedural languages, such as PL/SQL, may also be used to perform operations in a database system. For purpose of illustration, and not by way of limitation, the rest of this document will be explained with reference to SQL statements and relational database structures such as tables and rows. It is noted, however, that the inventive concepts disclosed herein are applicable to other types of objects, structures, and operations in a database.
A cursor is a handle to a query execution area, e.g., an area in memory in which a parsed SQL statement and other information for processing the SQL statement is stored. In many database systems, a cursor is an available resource for accessing or parsing SQL statements embedded within an application. Cursors enable a SQL statement to retrieve rows from a database table into a given result set, which can then be accessed, displayed, or operated upon.
Typically, a defined sequence of actions is performed to execute a SQL statement. To execute a SQL statement, the cursor is first created, either in conjunction with the SQL query or independently. The cursor may be automatically handled by the database system, or manually controlled by a programmer through an application programming interface. The SQL statement is thereafter parsed, optimized, and executed to produce a set of rows called the result set. The rows in the result set are retrieved (“fetched”) either a row at a time or in groups. The cursor can be considered the database query and its result set. A final stage for processing the SQL query statement is to close the cursor.
A significant level of overhead and expense is normally required to create a cursor, particularly due to the sheer amount of work involved to parse a SQL statement and to generate an execution plan for that statement. FIG. 1 is a flowchart illustrating some of the basic compilation steps that may be performed to create a cursor. In this shown approach, the SQL compilation process begins with a parse phase 150. The parse phase is so named because the SQL statement is analyzed and parsed (clause by clause) into its constituent components to create an expression tree (or parse tree). The parse phase involves syntactical analysis where the statement is analyzed for correct syntax (105) followed by a verification action, where among other things, a determination is made whether the referenced objects exist (110). User permissions may be analyzed to determine whether the requester holds sufficient access privileges to the specific objects referenced in the SQL text (115). The parse phase concludes by generating an expression tree (120). Type checking may be performed to engage data type resolution between a client process and a server process, which verifies and corrects data type incompatibilities, for example, in a heterogeneous enterprise network/system (125). An optimizer accepts the parsed and analyzed statement from the parse phase to determine an appropriate method to best execute the particular SQL statement based upon a number of criteria, including gathered statistical information, optimization methodologies, and/or selectivity analysis (130). The result is a memory resident data structure that dictates an execution plan for carrying out the database statement request. A cursor is an example of a memory structure that includes a handle to a memory location where the details and results of a parsed and optimizes statement resides. A cursor in this context is distinguishable from the use of the term cursor from other data processing contexts, such as client-side procedural language originated cursors used to aid data table processing or as a pointer to a screen location.
Due to this expense, it is often inefficient to recreate a cursor each and every time a client requests execution of a particular SQL statement. Instead, the cursor can be cached and reused to execute the same SQL statement. Even if a first SQL statement is not exactly the same as a second SQL statement, techniques have been developed to identify when cursors can be shared between statements that are sufficiently similar. Once example approach for sharing cursors and execution plans for database statements is described in co-pending U.S. application Ser. No. 10/086,277, filed on Feb. 28, 2002, entitled “Systems and Methods for Sharing of Execution Plans for Similar Database Statements”, which is hereby incorporated by reference in its entirety.
Embodiments of the present invention provide improved methods, systems, and mediums for implementing a dynamic cursor cache. According to an embodiment, cached cursors are tagged to facilitate identification and reuse of cursors from a cursor cache. According to another embodiment, cursor caches are managed in an integrated and/or coordinated manner between multiple nodes that have related sets of cursors or cursor resources. Further details of aspects, objects, and advantages of the invention are described in the detailed description, drawings, and claims.